package cn.com.dycc.util;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Set;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFFont;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.web.multipart.MultipartFile;

import cn.com.dycc.common.entity.ImportEntity;

public class ExcelUtil {
	//总行数
	private int totalRows = 0;
	//总条数
    private int totalCells = 0;
  //错误信息接收器
    private String errorMsg;
    
    
	
	public int getTotalRows() {
		return totalRows;
	}

	public void setTotalRows(int totalRows) {
		this.totalRows = totalRows;
	}

	public int getTotalCells() {
		return totalCells;
	}

	public void setTotalCells(int totalCells) {
		this.totalCells = totalCells;
	}

	public String getErrorMsg() {
		return errorMsg;
	}

	public void setErrorMsg(String errorMsg) {
		this.errorMsg = errorMsg;
	}
	
	
	/**
	 * 读EXCEL文件
	 * @param fileName 文件名称
	 * @param file 接受上传文件对象
	 * @param class1 实体类
	 * @param map key:实体类属性名 value:实体类属性名对应的中文名称
	 * @return 实体类集合
	 */
	public List<Object> getExcelInfo(String fileName,MultipartFile file,Class class1,Map<String, String> map){
		//服务器文件上传临时路径 
    	String temp_path=PathUtil.temp_path;
    	String file_path=temp_path+System.currentTimeMillis()+fileName.substring(fileName.lastIndexOf("."));
    	//初始化输入流
		InputStream is=null;
		File file2=null;
		
		List<Object> list=new ArrayList<Object>();
		try {			
			if (file != null) {          	
	        	//保存文件
	        	//判断文件是否为空
	        	if(!file.isEmpty()){
	        		//目录
	        		File filepath=new File(temp_path);
	        		if(!filepath.exists()){//判断改路径是否存在 
	                    filepath.mkdirs(); 
	                }
	        	}
	        	file2=new File(file_path);
	        	// 转存文件  
	            file.transferTo (file2); 	              
	        }					
			//验证文件名是否合格
			if(!validateExcel(fileName)){
				return list;
			}
			//根据文件名判断文件是2003版本还是2007版本
			boolean isExcel2003=true;
			if(WDWUtil.isExcel2007(fileName)){
				isExcel2003=false;
			}
			//根据新建的文件实例化输入流
			is=new FileInputStream(file2);
			list=getExcelInfo(is, isExcel2003,class1,map);
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if(is!=null){
				try {
					is.close();
				} catch (IOException e) {
					e.printStackTrace();
					is=null;
				}
			}
			if(file2!=null){
				file2.delete();
			}
		}
		return list;
		
		
	}
	
	/**
	 * 读取excel信息
	 * @param is 输入流
	 * @param isExcel2003 excel是2003还是2007版本
	 * @param obj 实体类
	 * @param map key:实体类属性名 value:实体类属性名对应的中文名称
	 * @return
	 */
	public List<Object> getExcelInfo(InputStream is,boolean isExcel2003,Class class1,Map<String, String>map){
		List<Object> list=new ArrayList<Object>();
		try {
			//根据版本选择创建Workbook的方式
			Workbook wb=null;
			if(isExcel2003){
				wb=new HSSFWorkbook(is);
			}else{
				wb=new XSSFWorkbook(is);
			}
			//读取excel信息
			list=readExcelValue(wb,class1,map);
		} catch (IOException e) {
			e.printStackTrace();
		}
		
		
		return list;
	}
	
	/**
	 * 读取excel
	 * @param wb 工作簿实体
	 * @param obj 实体类
	 * @param map key:实体类属性名 value:实体类属性名对应的中文名称
	 * @return
	 */
	public List<Object> readExcelValue(Workbook wb,Class class1,Map<String, String> map){
		List<Object> list=new ArrayList<Object>();
		try {
			//得到第一个shell
			Sheet sheet=wb.getSheetAt(0);
			//得到Excel的行数
			this.totalRows=sheet.getPhysicalNumberOfRows();
			//得到excel的行数(前提是有行数)
			if(totalRows>1&&sheet.getRow(0)!=null){
				this.totalCells=sheet.getRow(0).getPhysicalNumberOfCells();
			}
			
			Row row = sheet.getRow(0);
			System.out.println("this.totalCells"+this.totalCells);
			System.out.println("this.totalRows"+this.totalRows);
			String[] attributes=new String[this.totalCells];
			String[] activity_attributes=null;
			StringBuilder builder=new StringBuilder();
			int i=0;
			//循环Excel的列,获得属性数组activity_attributes
			for(int c = 0; c <this.totalCells; c++){
				Cell cell = row.getCell(c);
				if(null!=cell){
					String title=cell.getStringCellValue();
                	Set<String> keys=map.keySet();
                	for(String key:keys){               		
                		String value=map.get(key);
                		if(value.equals(title)){
                			attributes[i]=key;
                			//记录有效列
                			if("".equals(builder.toString())){
                        		builder.append(String.valueOf(c));
                        	}else{
        						builder.append(","+String.valueOf(c));
        					}
                			i++;
                			break;
                		}
                	}
                	
                	activity_attributes=new String[i];
                	System.arraycopy(attributes, 0, activity_attributes, 0, i);
				}
			}
			//Class class1=obj.getClass();
			//循环Excel行数,从第二行开始。标题不入库
			for(int r=1;r<totalRows;r++){
				Row row1 = sheet.getRow(r);
				if (row1 == null) continue;
				Object entity=class1.newInstance();
				//如果没有有效列,退出循环
				if("".equals(builder.toString())){
					break;
				}
				String[]cells=builder.toString().split(",");
				boolean flag=false;//过滤空行
				//读取与属性对应的电子表格信息
				for(int j=0;j<cells.length;j++){
					
					int c=Integer.valueOf(cells[j]);
					//与属性对应列
					Cell cell = row1.getCell(c);
					String cell_value="";
								
					if (null != cell){
						if(cell.getCellType()==Cell.CELL_TYPE_NUMERIC){
							double value=cell.getNumericCellValue();
							cell_value=String.valueOf(value);
							if(cell_value.indexOf(".0")==(cell_value.length()-2)){
								cell_value=cell_value.substring(0, cell_value.indexOf(".0"));
							}
							
						}else if(cell.getCellType()==Cell.CELL_TYPE_STRING){
							cell_value=cell.getStringCellValue();
						}
						Method method=class1.getMethod("set"+StringUtil.toUpperCaseFirstOne(activity_attributes[j]),String.class);
	                	if(!"".equals(cell_value)){
	                		flag=true;
	                	}
						method.invoke(entity, cell_value);
		        	}
				}
				if(flag){
					list.add(entity);
				}
				
				
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		
		return list;
		
	}
	
	/**
	 * 验证EXCEL文件
	 * @param filePath
	 * @return
	 */
	public boolean validateExcel(String filePath){
        if (filePath == null || !(WDWUtil.isExcel2003(filePath) || WDWUtil.isExcel2007(filePath))){  
            errorMsg = "文件名不是excel格式";  
            return false;  
        }  
        return true;
	}

	/**
	 * 
	 * @param data 数据表数据
	 * @param imports 表名与字段名对应实体类集合
	 * @param workbook 工作簿
	 * @param sheet 工作表
	 * @param startRow 起始行号
	 */
	public static void createExcel(List<Object> data,List<ImportEntity> imports,HSSFWorkbook workbook, HSSFSheet sheet, int startRow){
		
		//生成标题
		startRow=createTitle(imports, workbook,sheet,startRow, true);
		//填充数据
		createTableData(imports, data, workbook,sheet,startRow, false);
		
	}
	
	/**
	 * 生成表格标题
	 * @param column_names 
	 * @param workbook
	 * @param sheet
	 * @param startRow
	 * @param firstRow
	 * @return
	 */
	private static int createTitle(List<ImportEntity> imports,HSSFWorkbook workbook, HSSFSheet sheet, int startRow, boolean firstRow){
		//动态生成首行标题
		if(imports!=null&&imports.size()>0){
			sheet=createCell(workbook,sheet,imports.size(),startRow,firstRow);
			for(int i=0;i<imports.size();i++){
				getHSSFCell(workbook,sheet,i,sheet.getRow(startRow).getCell(i), imports.get(i).getName(), (short)11, HSSFCellStyle.ALIGN_CENTER, HSSFFont.BOLDWEIGHT_BOLD);
			}
			startRow+=1;
		}
		return startRow;
	}
	
	/**
	 * 填充数据
	 * @param imports
	 * @param data
	 * @param workbook
	 * @param sheet
	 * @param startRow
	 * @param firstRow
	 * @return
	 */
	private static int createTableData(List<ImportEntity> imports,List<Object> data,HSSFWorkbook workbook, HSSFSheet sheet, int startRow, boolean firstRow){
		StringBuilder builder=new StringBuilder();
		
		//遍历人员资料结果集
		if(imports!=null&&imports.size()>0){
			for(int j=0;j<data.size();j++){
				
				sheet=createCell(workbook,sheet,imports.size(),startRow,firstRow);
				
				for(int i=0;i<imports.size();i++){
					Object obj=data.get(j);
					Method method;
					try {
						method = obj.getClass().getMethod(builder.append("get").append(StringUtil.toUpperCaseFirstOne(imports.get(i).getValue())).toString());
						Object entity=method.invoke(obj);
						if(entity==null){
							entity="";
						}
						getHSSFCell(workbook,sheet,i,sheet.getRow(startRow).getCell(i), String.valueOf(entity), (short)11, HSSFCellStyle.ALIGN_CENTER, HSSFFont.BOLDWEIGHT_NORMAL);
					} catch (NoSuchMethodException e) {
						e.printStackTrace();
					} catch (SecurityException e) {
						e.printStackTrace();
					} catch (IllegalAccessException e) {
						e.printStackTrace();
					} catch (IllegalArgumentException e) {
						e.printStackTrace();
					} catch (InvocationTargetException e) {
						e.printStackTrace();
					}
					builder.setLength(0);
					
					
				}
				startRow+=1;
			}
			
			
		}
		return startRow;
	}
	
	/**
	 * 生成单元格
	 * @param workbook
	 * @param sheet
	 * @param count
	 * @param rowNum
	 * @param firstRow
	 * @return
	 */
	private static HSSFSheet createCell(HSSFWorkbook workbook,HSSFSheet sheet,int count,int rowNum,boolean firstRow){
		HSSFRow row = sheet.createRow(rowNum); 
		for(int i=0;i<count;i++){
			HSSFCell cell = row.createCell(i);
			getHSSFCell(workbook,sheet,i,cell,"",(short)11,HSSFCellStyle.ALIGN_CENTER,HSSFFont.BOLDWEIGHT_NORMAL);
			//sheet.setColumnWidth(i, 3766);
			/*sheet.setAutobreaks(true);
			sheet.autoSizeColumn((short) i);*///自动根据长度调整单元格长度 
			/*if(firstRow){
				row.setHeight((short)650);
			}
			sheet.setAutobreaks(true); 
			sheet.setColumnWidth((short)i,3766); //设定单元格长度 
		 	sheet.autoSizeColumn((short) i);//自动根据长度调整单元格长度 
			*/			
						 
		}
		return sheet;
	}
	
	/**
	 * 为单元格添加内容和样式
	 * @param workbook 工作簿
	 * @param cell 列
	 * @param value 内容
	 * @param fontSize 字体大小
	 * @param align 对齐方式
	 * @return
	 */
	private static HSSFCell getHSSFCell(HSSFWorkbook workbook,HSSFSheet sheet,int i,HSSFCell cell,String value,short fontSize,short align,short flagBold){
		HSSFCellStyle cellStyle = workbook.createCellStyle(); // 单元格样式
		Font fontStyle = workbook.createFont(); // 字体样式
		fontStyle.setBoldweight(flagBold); // 加粗
		fontStyle.setFontName("黑体"); // 字体
		fontStyle.setFontHeightInPoints(fontSize); // 大小
		// 将字体样式添加到单元格样式中 
		cellStyle.setFont(fontStyle);		
		// 边框，居中
		cellStyle.setAlignment(align);//左右居中
		cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);//下边框 
		cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框   
		cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框 
		cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框 
		cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
		cellStyle.setRightBorderColor(HSSFColor.BLACK.index);
		cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
		cellStyle.setLeftBorderColor(HSSFColor.BLACK.index);
		cellStyle.setWrapText(true);//设置自动换行
		
		if(value!=null&&!value.trim().isEmpty()){
			cell.setCellValue(value);//单元格填充内容 
		}
			
		cell.setCellStyle(cellStyle);//为单元格添加样式
		sheet.setAutobreaks(true);
		sheet.autoSizeColumn((short) i);
		return cell;
	}

}
